Create Fact Tables and Populate the Tables 10

CREATE FACT TABLE AND POPULATE THE TABLES


Building the Data Warehouse Database and all data objects


 Click the download [ ] image to download the Data Warehouse script.

Extract the file and execute the scripts in MSSQL Server Management.


Open the extracted folder and you will see two folders ( StoredProcedure and Tables ) as shown below.


Open script with SSMS 18 then Executes the scripts one after the other till all three scripts have been executed. 




You may also open in Notepad, copy the content into SSMS  and execute to build all four database objects.

Execute all three scripts in the folder like shown below

Next, open the StoredProcedure Folder and Execute the script to generate the Stored Procedure as shown below


Verify that all four data objects can be seen in the Object explorer as shown below


If you do not see all objects, it means you omitted some of the steps. review and make the necessary corrections before proceeding.

However, if all objects can be seen then we have completed our first task which is DataBase and Object creation.

Building SSIS Package to load the Fact Table.

Building SSIS Package t


Building SSIS Package to Load the Fact Table

Creating the Package

Add a new package as shown in image below

Rename the package to make it more descriptive


Loading FactPprojectStatus


Double click on the newly created package and then begin to drag and drop our Data flow Task as  shown below and rename the package to make it discriptive.

 

The DataFlow task workspace will be open. Drag and Drop an OLE DB Source like shown below. 

Double click to begin with configuration . Select Connection Manager. Click New and establish the 




Configure the Connection Manager like shown below then click Ok when done.

Below is the script to use

select [CCS_PR_ID], [Created], [UserID], [PreUserID], [CCS_PR_Approved],[Username],
case   [CCS_PR_Approved] 
when  0 then 0
when 1 then 1
when 2 then 1
end FristApprovalStatus,

Case  [CCS_PR_Approved] 
when  0 then 0
when 1 then 0
when 2 then 1
end SecondApprovalStatus,
[Updated],
[UpdatedBy]
from [dbo].[ADF_CCS_ProjectRequest] CPP
left join [dbo].[ADF_Users] ADU
on CPP.CreatedBy = ADU.[UserID]

Click Ok when done.


Drag and drop a Lookup transformational nd connect the OLEDB Source as shown in the image below and apply the configuration below.



Add another lookup transformation and name it TagKey as shown below. Then like the projectKey Lookup to it


Double click on the TagKey and configure as shown below

Click Ok when done configuring the TagKey LookUp.

Add another LookUp transformation and configure as shown below.


Double click on the UserKey to begin configuration as sown below

Add another LookUp transformation as shown below


Now for Non-Matching Output, we will be connecting  to OLE DB Destination as shown below

Double-click on the OLE DB Destination and begin to configure as shown below.


Double Click on the lookUp transformation as shown below and configure as shown

Configure the columns and when done, click Ok to save the configuration


Add OLE DB Command and connect to the LookUp as shown below and click Ok.


Double click on the OLE DB Command and begin to configure as shown below.





Update [dbo].[FactProjectStatus]
set 
[Created] =?, 
[CreatedBy]=?, 
[FristApprovalStatus]=?, 
[SecondApprovalStatus]=?,
 [Updated]=?, 
[UpdatedBy] =?
where [ProjectStatusKey] =?


Perform the following Mapping and click Ok when done.

Testing



Loading FactClickThroughTracking

Drag and drop dataFlow task on the control Flow as seen in the image, rename and double click to begin configuration.

In the Data Flow task drag and drop an OLE DB Source and begin to configure as shown then click Ok when done


use the script below

with cte_FactClick
as
(
select [CCS_PR_ID], [CCS_PR_UserID], [CCS_PR_Approved], [IsActive], [ispublic],
[TagID], [PreUserID],[CreatedDate],
ACT.[Id],[StepOrder],[SourceID], [ProjectTagID]
from [dbo].[ADF_Pre_Users] APU
left join [dbo].[ADF_CCS_ProjectRequest] CPR
on  CPR.[CCS_PR_UserID] = APU.[ID]
left join  [dbo].[ADF_CCS_ClickThrough_Tracking] ACT
on CPR.CCS_PR_ID = ACT.ProjectId
left join [dbo].[ADF_CCS_ProjectTags] CPT
on CPR.CCS_PR_ID = CPT.ProjectID
)
select * from cte_FactClick


Add a LookUp transformation, rename and connect OLE DB source to it as shown. Double-Click and begin to configure.


Add another LookUp Transformation and begin to configure like shown

Double-Click and begin to configure as shown below

Add another LoookUp transformation as shown below and begin to configure

Double click on the PreUserKey LookUp transformation and begin to configure as shown below



Add another KookUp Transformation, name it UserKey and connect to PreUser Transformation as shown below.

Double Click the Userkey LookUp as shown below


Now that we have derived all the Keys, let us check if the record already exists by performing another LookUp Transformation as shown below.



Click Ok when done then add OLE DB Destination as shown below and link all Non-Matching output like shown below.

Double click on the OLE Destination and configure as shown below


Click Ok when done.

It is now time to configure all matching output by adding another LookUp Transformation then rename it to UpdateRecords as shown below


Double click to begin configuration as shown below.


Click Ok when done. Add OLE DB Command like shown below and connect all Non-Matching Records from the LookUp Transformation to it as shown below

Double click on the OLE DB Command and configure as shown

Click Ok when done.


Testing




Loading FactClickThroughPenetrationRate


This time we will be using Execute SQL Task to call the Stored Procedure "proc_Load_FactClickThorughPenetrationRate" we executed earlier on.

Add an Execute SQL Task and rename it as shown below.

Double on the Execute SQL Task and select the data connection as shown below 


Next  begin with configuration as shown below


Testing



Types of Fact Tables:

https://www.nuwavesolutions.com/fact-tables/

Populating Fact Tables:

https://dwbi1.wordpress.com/2015/02/09/populating-fact-tables/#:~:text=Fact%20tables%20are%20normally%20loaded,natural%20keys%20into%20surrogate%20keys.

Fact Table Loading:

https://mindmajix.com/ssis/fact-table-loading